package pers.vic.util.excel.other;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.compress.utils.Lists;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.exception.ExceptionUtils;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Comment;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import pers.vic.util.ReflectionUtils;
import pers.vic.util.codec.Encodes;
import pers.vic.util.excel.ExcelHeader;
import pers.vic.util.excel.annotation.ExcelField;


/**
 * 导出Excel文件（导出“XLSX”格式，支持大数据量导出   @see org.apache.poi.ss.SpreadsheetVersion）.
 * @author
 * 使用方法：
 * 1.单sheet：new ExportExcel(title, Test.class).setDataList(list).write(response, fileName).dispose();
 * @ExcelField(title="姓名",  align=1, sort=3, width = "6000")
	public String getName() {	return name;}
	2.多sheet：ExportExcel.exportExcelForMoreSheet(titleList, dataList).writeFile(response, fileName).dispose()
 */
public class ExportExcel {
	
	private static Logger log = LoggerFactory.getLogger(ExportExcel.class);
			
	/**
	 * 工作薄对象
	 */
	private SXSSFWorkbook wb;
	
	/**
	 * 工作表对象
	 */
	private SXSSFSheet sheet;
	
	/**
	 * 样式列表
	 */
	private Map<String, CellStyle> styles;
	
	/**
	 * 当前行号
	 */
	private int rownum;
	
	/**
	 * 注解列表（Object[]{ ExcelField, Field/Method }）
	 */
	List<Object[]> annotationList = new ArrayList<Object[]>()/*Lists.newArrayList()*/;
	
	/**
	 * 构造函数
	 * @param title 表格标题，传“空值”，表示无标题
	 * @param cls 实体对象，通过annotation.ExportField获取标题
	 */
	public ExportExcel(String title, Class<?> cls){
		this(title, cls, 1);
	}
	/**
	 * 导出函数，多sheet页导出
	 */
	public static ExportExcel exportExcelForMoreSheet(List<String> title, List<List<?>> dataList){
		ExportExcel ss = null;
		int i = 0;
		for (List<?> list : dataList) {
			if(list.size() > 0){
				Class<?> cls = list.get(0).getClass();
				if(i == 0){
					ss = new ExportExcel(title.get(i), cls, 1);
					List<ExcelHeader> headerList = ss.getHeaderList(cls, 1);
					ss.setDataListForMoreSheet(title.get(i), list, false, headerList);
				}else{
					List<ExcelHeader> headerList = ss.getHeaderList(cls, 1);
					ss.setDataListForMoreSheet(title.get(i), list, true, headerList);
				}
			}
			i++;
		}
		return ss;
	}
	
	/**
	 * 构造函数
	 * @param title 表格标题，传“空值”，表示无标题
	 * @param cls 实体对象，通过annotation.ExportField获取标题
	 * @param type 导出类型（1:导出数据；2：导出模板）
	 * @param groups 导入分组
	 */
	public ExportExcel(String title, Class<?> cls, int type, int... groups){
		// Get annotation field 
		Field[] fs = cls.getFields();
		for (Field f : fs){
			ExcelField ef = f.getAnnotation(ExcelField.class);
			if (ef != null && (ef.type()==0 || ef.type()==type)){
				if (groups!=null && groups.length>0){
					boolean inGroup = false;
					for (int g : groups){
						if (inGroup){
							break;
						}
						for (int efg : ef.groups()){
							if (g == efg){
								inGroup = true;
								annotationList.add(new Object[]{ef, f});
								break;
							}
						}
					}
				}else{
					annotationList.add(new Object[]{ef, f});
				}
			}
		}
		// Get annotation method
		Method[] ms = cls.getMethods();
		for (Method m : ms){
			ExcelField ef = m.getAnnotation(ExcelField.class);
			if (ef != null && (ef.type()==0 || ef.type()==type)){
				if (groups!=null && groups.length>0){
					boolean inGroup = false;
					for (int g : groups){
						if (inGroup){
							break;
						}
						for (int efg : ef.groups()){
							if (g == efg){
								inGroup = true;
								annotationList.add(new Object[]{ef, m});
								break;
							}
						}
					}
				}else{
					annotationList.add(new Object[]{ef, m});
				}
			}
		}
		// Field sorting
		Collections.sort(annotationList, new Comparator<Object[]>() {
			public int compare(Object[] o1, Object[] o2) {
				return new Integer(((ExcelField)o1[0]).sort()).compareTo(
						new Integer(((ExcelField)o2[0]).sort()));
			};
		});
		// Initialize
		List<ExcelHeader> headerList = Lists.newArrayList();
		for (Object[] os : annotationList){
			String t = ((ExcelField)os[0]).title();
			String width = ((ExcelField)os[0]).width();
			// 如果是导出，则去掉注释
			if (type==1){
				String[] ss = StringUtils.split(t, "**", 2);
				if (ss.length==2){
					t = ss[0];
				}
			}
			ExcelHeader header = new ExcelHeader.Builder().name(t).width(width).bulid();
			headerList.add(header);
		}
		initialize(title, headerList);
	}
	
	public List<ExcelHeader> getHeaderList(Class<?> cls,int type, int... groups){
		annotationList.clear();	
		// Get annotation field 
				Field[] fs = cls.getFields();
				for (Field f : fs){
					ExcelField ef = f.getAnnotation(ExcelField.class);
					if (ef != null && (ef.type()==0 || ef.type()==type)){
						if (groups!=null && groups.length>0){
							boolean inGroup = false;
							for (int g : groups){
								if (inGroup){
									break;
								}
								for (int efg : ef.groups()){
									if (g == efg){
										inGroup = true;
										annotationList.add(new Object[]{ef, f});
										break;
									}
								}
							}
						}else{
							annotationList.add(new Object[]{ef, f});
						}
					}
				}
				// Get annotation method
				Method[] ms = cls.getMethods();
				for (Method m : ms){
					ExcelField ef = m.getAnnotation(ExcelField.class);
					if (ef != null && (ef.type()==0 || ef.type()==type)){
						if (groups!=null && groups.length>0){
							boolean inGroup = false;
							for (int g : groups){
								if (inGroup){
									break;
								}
								for (int efg : ef.groups()){
									if (g == efg){
										inGroup = true;
										annotationList.add(new Object[]{ef, m});
										break;
									}
								}
							}
						}else{
							annotationList.add(new Object[]{ef, m});
						}
					}
				}
				// Field sorting
				Collections.sort(annotationList, new Comparator<Object[]>() {
					public int compare(Object[] o1, Object[] o2) {
						return new Integer(((ExcelField)o1[0]).sort()).compareTo(
								new Integer(((ExcelField)o2[0]).sort()));
					};
				});
				// Initialize
				List<ExcelHeader> headerList = Lists.newArrayList();
				for (Object[] os : annotationList){
					String t = ((ExcelField)os[0]).title();
					String width = ((ExcelField)os[0]).width();
					// 如果是导出，则去掉注释
					if (type==1){
						String[] ss = StringUtils.split(t, "**", 2);
						if (ss.length==2){
							t = ss[0];
						}
					}
					ExcelHeader header = new ExcelHeader.Builder().name(t).width(width).bulid();
					headerList.add(header);
				}
				return headerList;
	}
	
	/**
	 * 构造函数
	 * @param title 表格标题，传“空值”，表示无标题
	 * @param headers 表头数组
	 */
	public ExportExcel(String title, String[] headers) {
		ArrayList<String> headerList = new ArrayList<String>(Arrays.asList(headers));
		List<ExcelHeader> excelHeaderList = new ArrayList<ExcelHeader>();
		for (String headerName : headerList) {
			ExcelHeader excelHeader = new ExcelHeader.Builder().name(headerName).width("").bulid();;
			excelHeaderList.add(excelHeader);
		}
		initialize(title, excelHeaderList);
	}
	
	/**
	 * 构造函数
	 * @param title 表格标题，传“空值”，表示无标题
	 * @param headerList 表头列表
	 */
	public ExportExcel(String title, List<ExcelHeader> headerList) {
		initialize(title, headerList);
	}
	
	/**
	 * 初始化函数
	 * @param title 表格标题，传“空值”，表示无标题
	 * @param headerList 表头列表
	 */
	private void initializeForMoreSheet(String title, List<ExcelHeader> headerList,boolean isnew) {
		if(isnew){
			this.wb = new SXSSFWorkbook(500);
		}
		this.sheet = wb.createSheet(title);
		this.styles = createStyles(wb);
		// Create title
		if (StringUtils.isNotBlank(title)){
			Row titleRow = sheet.createRow(rownum++);
			titleRow.setHeightInPoints(30);
			Cell titleCell = titleRow.createCell(0);
			titleCell.setCellStyle(styles.get("title"));
			titleCell.setCellValue(title);
			sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(),
					titleRow.getRowNum(), titleRow.getRowNum(), headerList.size()-1));
		}
		// Create header
		if (headerList == null){
			throw new RuntimeException("headerList not null!");
		}
		Row headerRow = sheet.createRow(rownum++);
		headerRow.setHeightInPoints(16);
		sheet.trackAllColumnsForAutoSizing();
		for (int i = 0; i < headerList.size(); i++) {
			Cell cell = headerRow.createCell(i);
			cell.setCellStyle(styles.get("header"));
			String[] ss = StringUtils.split(headerList.get(i).getName(), "**", 2);
			if (ss.length==2){
				cell.setCellValue(ss[0]);
				Comment comment = this.sheet.createDrawingPatriarch().createCellComment(
						new XSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6));
				comment.setString(new XSSFRichTextString(ss[1]));
				cell.setCellComment(comment);
			}else{
				cell.setCellValue(headerList.get(i).getName());
			}
			sheet.autoSizeColumn(i);
		}
		for (int i = 0; i < headerList.size(); i++) {  
			int colWidth = sheet.getColumnWidth(i)*2;
			if(StringUtils.isEmpty(headerList.get(i).getWidth())){
		        sheet.setColumnWidth(i, colWidth < 3000 ? 3000 : colWidth);  
			}else{
				sheet.setColumnWidth(i, Integer.valueOf(headerList.get(i).getWidth())); 
			}
		}
		log.debug("Initialize success.");
	}
	
	/**
	 * 初始化函数
	 * @param title 表格标题，传“空值”，表示无标题
	 * @param headerList 表头列表
	 */
	private void initialize(String title, List<ExcelHeader> headerList) {
		this.wb = new SXSSFWorkbook(500);
		this.sheet = wb.createSheet(title);
		this.styles = createStyles(wb);
		// Create title
		if (StringUtils.isNotBlank(title)){
			Row titleRow = sheet.createRow(rownum++);
			titleRow.setHeightInPoints(30);
			Cell titleCell = titleRow.createCell(0);
			titleCell.setCellStyle(styles.get("title"));
			titleCell.setCellValue(title);
			sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(),
					titleRow.getRowNum(), titleRow.getRowNum(), headerList.size()-1));
		}
		// Create header
		if (headerList == null){
			throw new RuntimeException("headerList not null!");
		}
		Row headerRow = sheet.createRow(rownum++);
		headerRow.setHeightInPoints(16);
		sheet.trackAllColumnsForAutoSizing();
		for (int i = 0; i < headerList.size(); i++) {
			Cell cell = headerRow.createCell(i);
			cell.setCellStyle(styles.get("header"));
			String[] ss = StringUtils.split(headerList.get(i).getName(), "**", 2);
			if (ss.length==2){
				cell.setCellValue(ss[0]);
				Comment comment = this.sheet.createDrawingPatriarch().createCellComment(
						new XSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6));
				comment.setString(new XSSFRichTextString(ss[1]));
				cell.setCellComment(comment);
			}else{
				cell.setCellValue(headerList.get(i).getName());
			}
			sheet.autoSizeColumn(i);
		}
		for (int i = 0; i < headerList.size(); i++) {  
			int colWidth = sheet.getColumnWidth(i)*2;
			if(StringUtils.isEmpty(headerList.get(i).getWidth())){
		        sheet.setColumnWidth(i, colWidth < 3000 ? 3000 : colWidth);  
			}else{
				sheet.setColumnWidth(i, Integer.valueOf(headerList.get(i).getWidth())); 
			}
		}
		log.debug("Initialize success.");
	}
	
	/**
	 * 创建表格样式
	 * @param wb 工作薄对象
	 * @return 样式列表
	 */
	private Map<String, CellStyle> createStyles(Workbook wb) {
		Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
		
		CellStyle style = wb.createCellStyle();
		style.setAlignment(HorizontalAlignment.CENTER);
		style.setVerticalAlignment(VerticalAlignment.CENTER);
		Font titleFont = wb.createFont();
		titleFont.setFontName("Arial");
		titleFont.setFontHeightInPoints((short) 16);
		titleFont.setBold(true);
		style.setFont(titleFont);
		styles.put("title", style);

		style = wb.createCellStyle();
		style.setVerticalAlignment(VerticalAlignment.CENTER);
		style.setBorderRight(BorderStyle.HAIR);
		style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
		style.setBorderLeft(BorderStyle.HAIR);
		style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
		style.setBorderTop(BorderStyle.HAIR);
		style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
		style.setBorderBottom(BorderStyle.HAIR);
		style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
		Font dataFont = wb.createFont();
		dataFont.setFontName("Arial");
		dataFont.setFontHeightInPoints((short) 10);
		style.setFont(dataFont);
		styles.put("data", style);
		
		style = wb.createCellStyle();
		style.cloneStyleFrom(styles.get("data"));
		style.setAlignment(HorizontalAlignment.LEFT);
		styles.put("data1", style);

		style = wb.createCellStyle();
		style.cloneStyleFrom(styles.get("data"));
		style.setAlignment(HorizontalAlignment.CENTER);
		styles.put("data2", style);

		style = wb.createCellStyle();
		style.cloneStyleFrom(styles.get("data"));
		style.setAlignment(HorizontalAlignment.RIGHT);
		styles.put("data3", style);
		
		style = wb.createCellStyle();
		style.cloneStyleFrom(styles.get("data"));
//		style.setWrapText(true);
		style.setAlignment(HorizontalAlignment.CENTER);
		style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
		style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		Font headerFont = wb.createFont();
		headerFont.setFontName("Arial");
		headerFont.setFontHeightInPoints((short) 10);
		headerFont.setBold(true);
		headerFont.setColor(IndexedColors.WHITE.getIndex());
		style.setFont(headerFont);
		styles.put("header", style);
		
		return styles;
	}

	/**
	 * 添加一行
	 * @return 行对象
	 */
	public Row addRow(){
		return sheet.createRow(rownum++);
	}
	

	/**
	 * 添加一个单元格
	 * @param row 添加的行
	 * @param column 添加列号
	 * @param val 添加值
	 * @return 单元格对象
	 */
	public Cell addCell(Row row, int column, Object val){
		return this.addCell(row, column, val, 0, Class.class);
	}
	
	/**
	 * 添加一个单元格
	 * @param row 添加的行
	 * @param column 添加列号
	 * @param val 添加值
	 * @param align 对齐方式（1：靠左；2：居中；3：靠右）
	 * @return 单元格对象
	 */
	public Cell addCell(Row row, int column, Object val, int align, Class<?> fieldType){
		Cell cell = row.createCell(column);
		String cellFormatString = "@";
		try {
			if(val == null){
				cell.setCellValue("");
			}else if(fieldType != Class.class){
				cell.setCellValue((String)fieldType.getMethod("setValue", Object.class).invoke(null, val));
			}else{
				if(val instanceof String) {
					cell.setCellValue((String) val);
				}else if(val instanceof Integer) {
					cell.setCellValue((Integer) val);
					cellFormatString = "0";
				}else if(val instanceof Long) {
					cell.setCellValue((Long) val);
					cellFormatString = "0";
				}else if(val instanceof Double) {
					cell.setCellValue((Double) val);
					cellFormatString = "0.00";
				}else if(val instanceof Float) {
					cell.setCellValue((Float) val);
					cellFormatString = "0.00";
				}else if(val instanceof Date) {
					cell.setCellValue((Date) val);
					cellFormatString = "yyyy-MM-dd HH:mm";
				}else {
					cell.setCellValue((String)Class.forName(this.getClass().getName().replaceAll(this.getClass().getSimpleName(), 
						"fieldtype."+val.getClass().getSimpleName()+"Type")).getMethod("setValue", Object.class).invoke(null, val));
				}
			}
			if (val != null){
				CellStyle style = styles.get("data_column_"+column);
				if (style == null){
					style = wb.createCellStyle();
					style.cloneStyleFrom(styles.get("data"+(align>=1&&align<=3?align:"")));
			        style.setDataFormat(wb.createDataFormat().getFormat(cellFormatString));
					styles.put("data_column_" + column, style);
				}
				cell.setCellStyle(style);
			}
		} catch (Exception ex) {
			log.info("Set cell value ["+row.getRowNum()+","+column+"] error: " + ex.toString());
			cell.setCellValue(val.toString());
		}
		return cell;
	}

	/**
	 * 添加数据（通过annotation.ExportField添加数据）
	 * @return list 数据列表
	 */
	public <E> ExportExcel setDataList(List<E> list){
		for (E e : list){
			int colunm = 0;
			Row row = this.addRow();			
			StringBuilder sb = new StringBuilder();
			for (Object[] os : annotationList){
				ExcelField ef = (ExcelField)os[0];
				Object val = null;
				// Get entity value
				try{
					if (StringUtils.isNotBlank(ef.value())){
						val = ReflectionUtils.invokeGetter(e, ef.value());
					}else{
						if (os[1] instanceof Field){
							val = ReflectionUtils.invokeGetter(e, ((Field)os[1]).getName());
						}else if (os[1] instanceof Method){
							val = ReflectionUtils.invokeMethod(e, ((Method)os[1]).getName(), new Class[] {}, new Object[] {});
						}
					}
					// If is dict, get dict label
					/*if (StringUtils.isNotBlank(ef.dictType())){
						val = DictUtils.getDictLabel(val==null?"":val.toString(), ef.dictType(), "");
					}*/
					
					if(ef.keys().length>0){
					    long[] keys= ef.keys();
					    long val_i=0;
					    if(val  instanceof String){
					        val_i=Long.parseLong((String)val);
					    }else if(val  instanceof Integer){
					    	Integer ix= (Integer) val;
					        val_i=ix.longValue();
					    }else{
					        val_i=(long)val;
					    }
					  
					    for(int i=0;i<keys.length;i++){
					        if(val_i==keys[i]){
					          val=ef.values()[i];
					          break;
					        }
					    }
					}
					if(ef.datePattern().length()>0){
					    long time=(long) val;
					    if(val.toString().length()<13){
					        time= time*1000L;
				           }
				           Date date= new Date(time);
				           SimpleDateFormat dateFormat=new SimpleDateFormat(ef.datePattern());
				           val = dateFormat.format(date);  
					}
				}catch(Exception ex) {
					// Failure to ignore
					//log.info(ex.toString());
					val = "";
				}
				this.addCell(row, colunm++, val, ef.align(), ef.fieldType());
				sb.append(val + ", ");
			}
//			log.debug("Write success: ["+row.getRowNum()+"] "+sb.toString());
		}
		return this;
	}
	
	/**
	 * 添加数据（通过annotation.ExportField添加数据），方便添加新的sheet页，isNewSheet = true  新加新sheet
	 * @return list 数据列表
	 */
	public <E> ExportExcel setDataListForMoreSheet(String title,List<E> list,boolean isNewSheet,List<ExcelHeader> headerList){
		if(isNewSheet){
			rownum = 0;
			initializeForMoreSheet(title,headerList,false);
		}
		for (E e : list){
			int colunm = 0;
			Row row = this.addRow();			
			StringBuilder sb = new StringBuilder();
			for (Object[] os : annotationList){
				ExcelField ef = (ExcelField)os[0];
				Object val = null;
				// Get entity value
				try{
					if (StringUtils.isNotBlank(ef.value())){
						val = ReflectionUtils.invokeGetter(e, ef.value());
					}else{
						if (os[1] instanceof Field){
							val = ReflectionUtils.invokeGetter(e, ((Field)os[1]).getName());
						}else if (os[1] instanceof Method){
							val = ReflectionUtils.invokeMethod(e, ((Method)os[1]).getName(), new Class[] {}, new Object[] {});
						}
					}
					// If is dict, get dict label
					/*if (StringUtils.isNotBlank(ef.dictType())){
						val = DictUtils.getDictLabel(val==null?"":val.toString(), ef.dictType(), "");
					}*/
					
					if(ef.keys().length>0){
					    long[] keys= ef.keys();
					    long val_i=0;
					    if(val  instanceof String){
					        val_i=Long.parseLong((String)val);
					    }else if(val  instanceof Integer){
					    	Integer ix= (Integer) val;
					        val_i=ix.longValue();
					    }else{
					        val_i=(long)val;
					    }
					  
					    for(int i=0;i<keys.length;i++){
					        if(val_i==keys[i]){
					          val=ef.values()[i];
					          break;
					        }
					    }
					}
					if(ef.datePattern().length()>0){
					    long time=(long) val;
					    if(val.toString().length()<13){
					        time= time*1000L;
				           }
				           Date date= new Date(time);
				           SimpleDateFormat dateFormat=new SimpleDateFormat(ef.datePattern());
				           val = dateFormat.format(date);  
					}
				}catch(Exception ex) {
					// Failure to ignore
					//log.info(ex.toString());
					val = "";
				}
				this.addCell(row, colunm++, val, ef.align(), ef.fieldType());
				sb.append(val + ", ");
			}
			log.debug("Write success: ["+row.getRowNum()+"] "+sb.toString());
		}
		return this;
	}
	
	/**
	 * 输出数据流
	 * @param os 输出数据流
	 */
	public ExportExcel write(OutputStream os) throws IOException{
		wb.write(os);
		return this;
	}
	
	/**
	 * 输出到客户端
	 * @param fileName 输出文件名
	 */
	public ExportExcel write(HttpServletResponse response, String fileName) throws IOException{
		response.reset();
        response.setContentType("application/octet-stream; charset=utf-8");
        response.setHeader("Content-Disposition", "attachment; filename="+Encodes.urlEncode(fileName));
		write(response.getOutputStream());
		return this;
	}
	
	/**
	 * 输出到文件
	 * @param fileName 输出文件名
	 */
	public ExportExcel writeFile(String name) throws FileNotFoundException, IOException{
		FileOutputStream os = new FileOutputStream(name);
		this.write(os);
		return this;
	}
	
	/**
	 * 清理临时文件
	 */
	public ExportExcel dispose(){
		wb.dispose();
		return this;
	}
	
//	/**
//	 * 导出测试
//	 */
	public static void main(String[] args) throws Throwable {
		
		List<ExcelHeader> headerList = new ArrayList<ExcelHeader>();
		for (int i = 1; i <= 10; i++) {
			headerList.add(new ExcelHeader(new ExcelHeader.Builder().name("表头"+i)));
		}
		
		List<String> dataRowList = Lists.newArrayList();
		for (int i = 1; i <= headerList.size(); i++) {
			dataRowList.add("数据"+i);
		}
		
		List<List<String>> dataList = Lists.newArrayList();
		for (int i = 1; i <=100; i++) {
			dataList.add(dataRowList);
		}

		ExportExcel ee = new ExportExcel("表格标题", headerList);
		
		for (int i = 0; i < dataList.size(); i++) {
			Row row = ee.addRow();
			for (int j = 0; j < dataList.get(i).size(); j++) {
				ee.addCell(row, j, dataList.get(i).get(j));
			}
		}
		
		ee.writeFile("E:\\export.xlsx");

		ee.dispose();
		
		log.debug("Export success.");
		
	}
	
	/**
	 * 下载多文件 打包文件可供下载
	 */
	public static void downLoadZip(HttpServletResponse response,List<String> filePath,String fileName){
		try {
			File zip = new File(fileName + ".zip");  
//			if (!zip.exists()){     
//			   zip.createNewFile();     
//			} 
		    //创建zip文件输出流  
		    FileOutputStream fos = new FileOutputStream(zip);
		    ZipOutputStream zos = new ZipOutputStream(fos);
		    //循环读取文件路径集合，获取每一个文件的路径  
		    for(String fp : filePath){
		        File f = new File(fp);  //根据文件路径创建文件  
		        zipFile(f, zos);  //将每一个文件写入zip文件包内，即进行打包  
		        f.delete();//删除临时文件
		    }
		    zos.close();
		    fos.close();
			response.reset();
	        response.setContentType("application/octet-stream; charset=utf-8");
	        response.setHeader("Content-Disposition", "attachment; filename="+Encodes.urlEncode(fileName + ".zip"));	    
	        //创建页面返回方式为输出流，会自动弹出下载框  
	        OutputStream out = response.getOutputStream();
		    //将打包后的文件写到客户端，输出的方法同上，使用缓冲流输出  
		    FileInputStream fileInput = new FileInputStream(zip);
		    InputStream fis = new BufferedInputStream(fileInput);  
		    try{
		    	byte[] buff = new byte[6144];
			    int size = 0;
			    while((size=fis.read(buff)) != -1){
			        out.write(buff, 0, size);
			    }
			    out.flush();
			    out.close();
		    }catch(Exception e){
		    	log.error(ExceptionUtils.getStackTrace(e));
		    }
		    fis.close();
		    fileInput.close();
		    zip.delete();//删除压缩包
		} catch (Exception e) {
			log.error(ExceptionUtils.getStackTrace(e));
		}
	}

	// 封装压缩文件的方法
	public static void zipFile(File inputFile, ZipOutputStream zipoutputStream) {
		try {
			if (inputFile.exists()) { // 判断文件是否存在
				if (inputFile.isFile()) { // 判断是否属于文件，还是文件夹
					// 创建输入流读取文件
					FileInputStream fis = new FileInputStream(inputFile);
					BufferedInputStream bis = new BufferedInputStream(fis);
					// 将文件写入zip内，即将文件进行打包
					ZipEntry ze = new ZipEntry(inputFile.getName()); // 获取文件名
					zipoutputStream.putNextEntry(ze);
					// 写入文件的方法，同上
					byte[] b = new byte[1024];
					long l = 0;
					while (l < inputFile.length()) {
						int j = bis.read(b, 0, 1024);
						l += j;
						zipoutputStream.write(b, 0, j);
					}
					// 关闭输入输出流
					fis.close();
					bis.close();
				} else { // 如果是文件夹，则使用穷举的方法获取文件，写入zip
					try {
						File[] files = inputFile.listFiles();
						for (int i = 0; i < files.length; i++) {
							zipFile(files[i], zipoutputStream);
						}
					} catch (Exception e) {
						log.error(ExceptionUtils.getStackTrace(e));
					}
				}
			}
		} catch (Exception e) {
			log.error(ExceptionUtils.getStackTrace(e));
		}
	}

}
